package com.nus.database;

import com.nus.beans.*;
import com.nus.database.util.DataReadUtils;
import com.nus.services.ConversionService;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by IntelliJ IDEA.
 * User: dell
 * Date: 9/26/12
 * Time: 12:18 AM
 * To change this template use File | Settings | File Templates.
 */
public class ProjectsDBManager extends DBManager {
    public ProjectsDBManager(Connection connection) {
        super(connection);
    }

    public void createProject(Project project, List<User> users) {
        PreparedStatement statement = null;
        try {
            setAutoCommit(false);
            int projectId = getNextProjectId();
            project.setId(projectId);
            createProjectEntry(project);
            assignUsers(project, users);
            commit();
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
            rollback();
        } finally {
            close(statement);
            setAutoCommit(true);
        }
    }

    public void createProjectEntry(Project project) throws SQLException {
        PreparedStatement statement = null;
        ConversionService conversionService = new ConversionService();
        int projectStatusDB;
        try {
            statement = connection.prepareStatement(SQLStatements.CREATE_PROJECT_SQL);
            statement.setInt(1, project.getId());
            statement.setString(2, project.getProjectName());
            statement.setDate(3, new java.sql.Date(project.getStartingDate().getTime()));
            statement.setDate(4, new java.sql.Date(project.getEstimatedEndDate().getTime()));
            statement.setDate(5, new java.sql.Date(project.getDeadline().getTime()));
            projectStatusDB = conversionService.getProjectStatusDB(project.getStatus());
            statement.setInt(6, projectStatusDB);
            statement.executeUpdate();

        } finally {
            close(statement);
        }
    }

    public void assignUsers(Project project, List<User> users) throws SQLException {
        PreparedStatement statement = null;
        for (User user : users) {
            statement = connection.prepareStatement(SQLStatements.ASSIGN_USER_TO_PROJECT);
            statement.setInt(1, user.getUserId());
            statement.setInt(2, project.getId());
            statement.executeUpdate();
            close(statement);
        }
    }

    private int getNextProjectId() throws SQLException {
        PreparedStatement statement = null;
        int projectId = -1;
        try {
            statement = connection.prepareStatement(SQLStatements.GET_PROJECT_ID_SQL);
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                projectId = rs.getInt(1);
            }

        } finally {
            close(statement);
        }
        return projectId;
    }

    public List<Project> loadProjects() {
        PreparedStatement statement = null;
        ResultSet resultSet;
        Project project;
        List<Project> projectsList = new ArrayList<Project>();
        try {
            statement = connection.prepareStatement(SQLStatements.GET_ALL_PROJECT_DATA_SQL);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                project = DataReadUtils.getProjectInformation(resultSet);
                projectsList.add(project);
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement);
        }
        return projectsList;
    }

    public Project getProject(long projectId) {
        PreparedStatement statement = null;
        ResultSet resultSet;
        Project project = null;
        try {
            statement = connection.prepareStatement(SQLStatements.GET_PROJECT_DATA_SQL);
            statement.setLong(1, projectId);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                project = DataReadUtils.getProjectInformation(resultSet);
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement);
        }
        return project;
    }

    public List<User> getProjectAssigneesList(int projectId) {
        PreparedStatement statement = null;
        ResultSet resultSet;
        User user;
        List<User> usersList = new ArrayList<User>();
        try {
            statement = connection.prepareStatement(SQLStatements.GET_PROJECT_ASSIGNEES);
            statement.setInt(1, projectId);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                user = DataReadUtils.getUserInformation(resultSet);
                usersList.add(user);
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement);
        }
        return usersList;
    }

    public void deleteProject(int projectId) {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(SQLStatements.DELETE_PROJECT_SQL);
            statement.setInt(1, projectId);
            statement.executeUpdate();

        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement);
        }
    }

    public void updateProject(Project project, List<User> usersToAdd, List<User> userToDelete) {
        PreparedStatement statement = null;
        try {
            setAutoCommit(false);
            updateProjectInstance(project);
            assignUsers(project, usersToAdd);
            removeUsers(project, userToDelete);
            commit();
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement);
            setAutoCommit(true);
        }
    }

    private void updateProjectInstance(Project project) throws SQLException {
        PreparedStatement statement = connection.prepareStatement(SQLStatements.UPDATE_PROJECT_SQL);
        int projectStatusDB;
        ConversionService conversionService = new ConversionService();
        statement.setString(1, project.getProjectName());
        statement.setDate(2, new java.sql.Date(project.getStartingDate().getTime()));
        statement.setDate(3, new java.sql.Date(project.getEstimatedEndDate().getTime()));
        statement.setDate(4, new java.sql.Date(project.getDeadline().getTime()));
        projectStatusDB = conversionService.getProjectStatusDB(project.getStatus());
        statement.setInt(5, projectStatusDB);
        statement.setInt(6, project.getId());
        statement.executeUpdate();
    }

    private void removeUsers(Project project, List<User> usersToRemove) throws SQLException {
        PreparedStatement statement = null;
        for (User user : usersToRemove) {
            statement = connection.prepareStatement(SQLStatements.DELTE_USER_OF_PROJECT);
            statement.setInt(1, user.getUserId());
            statement.setInt(2, project.getId());
            statement.executeUpdate();
            close(statement);
        }
    }

    public List<User> getUsersListForProjects() {
        PreparedStatement statement = null;
        ResultSet resultSet;
        List<User> usersList = new ArrayList<User>();
        User user;
        try {
            statement = connection.prepareStatement(SQLStatements.GET_PROJECT_USERS);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                user = DataReadUtils.getUserInformation(resultSet);
                usersList.add(user);
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement);
        }
        return usersList;
    }

    public List<ProjectRemark> getProjectRemarks(long projectId) {
        PreparedStatement statement = null;
        ResultSet resultSet;
        List<ProjectRemark> projectRemarkList = new ArrayList<ProjectRemark>();
        ProjectRemark projectRemark;
        try {
            statement = connection.prepareStatement(SQLStatements.GET_PROJECT_REMARKS_BY_PROJECT_ID);
            statement.setLong(1, projectId);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                projectRemark = DataReadUtils.getProjectRemark(resultSet);
                projectRemarkList.add(projectRemark);
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement);
        }
        return projectRemarkList;
    }

   public void createProjectRemark(ProjectRemark projectRemark) {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(SQLStatements.CRAETE_PROJECT_REMARK);
            statement.setLong(1, projectRemark.getProjectId());
            statement.setString(2, projectRemark.getRemark());
            statement.setLong(3, projectRemark.getEnteredBy().getUserId());
            statement.executeQuery();
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement);
        }
    }

    public ProjectRemark getProjectRemark(int projectRemarkId) {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        ProjectRemark projectRemark = null;
        try {
            statement = connection.prepareStatement(SQLStatements.GET_PROJECT_REMARKS_BY_ID);
            statement.setLong(1, projectRemarkId);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                projectRemark = DataReadUtils.getProjectRemark(resultSet);
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement, resultSet);
        }
        return projectRemark;
    }

    public void deleteProjectRemark(int projectRemarkId) {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(SQLStatements.DELETE_PROJECT_REMARKS_BY_ID);
            statement.setLong(1, projectRemarkId);
            statement.executeQuery();
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement);
        }
    }

    public void updateProjectRemark(long projectRemarkId, String remark) {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(SQLStatements.UPDATE_PROJECT_REMARK);
            statement.setString(1, remark);
            statement.setLong(2, projectRemarkId);
            statement.executeQuery();
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement);
        }
    }

}
